/**
 * 
 */
package fr.greta.centreFormation.dao;

import java.rmi.UnexpectedException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import fr.greta.centreFormation.bean.Module;
import fr.greta.centreFormation.bean.UniteEnseignement;

/**
 * @author Betty Boop
 *
 */
public class ModuleHome {

	public static int insertModule(Module unModule){
		String intituleModule = unModule.getIntituleModule();
		String descriptionModule = unModule.getDescriptionModule();

		String sql = "INSERT INTO module(intitule_module, description_module) VALUE (?,?)";
		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

			stmt.setString(1, intituleModule);
			stmt.setString(2, descriptionModule);

			stmt.executeUpdate();
			ResultSet result = stmt.getGeneratedKeys();
			result.next();

			int idModuleInsere = result.getInt(1);
			return idModuleInsere;

		} catch (Exception e) {
			e.getStackTrace();
			return 0;}
	}
	public static boolean deleteModule (Module unModule){
		int idModule = unModule.getIdModule();
		String sql = "DELETE FROM module WHERE id_module ="+idModule;

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);

			stmt.execute();

			return true;
		} catch (Exception e) {
			e.getStackTrace();
			return false;
		}


	}
	
	public static boolean updateModule(Module unModule){
		int idModule = unModule.getIdModule();
		String update = "UPDATE module SET intitule_module = ?, description_module = ? WHERE id_module = ?";
		
		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(update);
			stmt.setString(1, unModule.getIntituleModule());
			stmt.setString(2, unModule.getDescriptionModule());
			stmt.setInt(3, idModule);
			
			stmt.execute();
			return true;
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	
	public static ArrayList<Module> selectAllModule(){
		ArrayList<Module> allModule = new ArrayList<Module>();
		String sql = "SELECT * FROM module";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet result = stmt.executeQuery();

			while(result.next()){
				Module module = new Module();
				module.setIdModule(result.getInt(1));
				module.setIntituleModule(result.getString(2));
				module.setDescriptionModule(result.getString(3));

				allModule.add(module);
			}
			return allModule;
		} catch (Exception e) {
			e.getStackTrace();
			return null;
		}
	}
	public static Module selectUnModuleById(int idModule){

		String sql = "SELECT * FROM module WHERE id_module ="+idModule;

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet result = stmt.executeQuery();

			result.next();
			Module unModule = new Module();
			unModule.setIdModule(result.getInt(1));
			unModule.setIntituleModule(result.getString(2));
			unModule.setDescriptionModule(result.getString(3));

			return unModule;

		} catch (Exception e) {
			e.getStackTrace();
			return null;
		}
	}


	public static ArrayList<Module> selectAllModuleByIdUE(int idUE){

		ArrayList<Module> allModule = new ArrayList<Module>();
		String sql ="SELECT * FROM module M INNER JOIN module_unite_enseignement MUE ON MUE.id_module=M.id_module WHERE MUE.id_unite_enseignement = "+idUE;

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet result = stmt.executeQuery();

			while (result.next()){
				Module unModule = new Module();
				unModule.setIdModule(result.getInt(1));
				unModule.setIntituleModule(result.getString(2));
				unModule.setDescriptionModule(result.getString(3));
				allModule.add(unModule);
			}
			return allModule;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static ArrayList<Module> selectAllModuleByUE(UniteEnseignement uneUE){
		int idUE=uneUE.getIdUniteEnseignement();
		ArrayList<Module> allModule = new ArrayList<Module>();
		String sql ="SELECT * FROM module M INNER JOIN module_unite_enseignement MUE ON MUE.id_module=M.id_module WHERE MUE.id_unite_enseignement = "+idUE;

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet result = stmt.executeQuery();

			while (result.next()){
				Module unModule = new Module();
				unModule.setIdModule(result.getInt(1));
				unModule.setIntituleModule(result.getString(2));
				unModule.setDescriptionModule(result.getString(3));
				allModule.add(unModule);
			}
			return allModule;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
}
